﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPPEI.ETL.Infrastructure
{
    public static class SQLTool
    {
        /// <summary>
        /// 获取删除表的sql语句
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static string GetDropTableSQL(string table)
        {
            StringBuilder sqlBuilder = new StringBuilder("DROP TABLE ");
            sqlBuilder.AppendFormat("[dbo].[{0}]", table);
           
            return sqlBuilder.ToString();
        }

        /// <summary>
        /// 创建数据库表
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pk"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static string GetCreateTableSQL(string table, List<string> pks, List<string> names, List<string> types, List<string> lengths, List<bool> nullables)
        {
            StringBuilder sqlBuilder = new StringBuilder("CREATE TABLE ");
            sqlBuilder.AppendFormat("[dbo].[{0}]", table);
            sqlBuilder.AppendLine("(");
            for (int index = 0; index < names.Count; index++)
            {
                string name = names[index];
                if (pks.Contains(name))
                {
                    // pk
                    sqlBuilder.AppendFormat("[{0}] {1} {2}", name, GetTypeString(types[index], lengths[index]), GetNullString(nullables[index]));
                    sqlBuilder.AppendLine(",");
                }
                else
                {
                    sqlBuilder.AppendFormat("[{0}] {1} {2}", name, GetTypeString(types[index], lengths[index]), GetNullString(nullables[index]));
                    sqlBuilder.AppendLine(",");
                }
            }
            sqlBuilder.AppendLine("PRIMARY KEY CLUSTERED (");
            for (int index = 0; index < pks.Count; index++)
            {
                if(index == 0)
                {
                    sqlBuilder.AppendFormat("[{0}]", pks[index]);
                }
                else
                {
                    sqlBuilder.Append(",");
                    sqlBuilder.AppendFormat("[{0}]", pks[index]);
                    sqlBuilder.AppendLine();
                }
            }
            sqlBuilder.Append(")");
            sqlBuilder.Append(")");

            return sqlBuilder.ToString();
        }

        public static string GetTypeString(string type, string length)
        {
            if(string.IsNullOrEmpty(length))
            {
                return type;
            }
            else if (string.Equals(type, "int", StringComparison.CurrentCultureIgnoreCase))
            {
                return type;
            }
            else if (string.Equals(type, "decimal", StringComparison.CurrentCultureIgnoreCase))
            {
                return type;
            }
            else if(string.Equals(type, "datetime", StringComparison.CurrentCultureIgnoreCase))
            {
                return type;
            }
            else if (string.Equals(type, "bit", StringComparison.CurrentCultureIgnoreCase))
            {
                return type;
            }
            else
            {
                return string.Format("{0}({1})", type, length);
            }
        }

        public static string GetNullString(bool nullable)
        {
            if(nullable)
            {
                return "NULL";
            }
            else
            {
                return "NOT NULL";
            }
        }

        public static string GetUpdateSQLV1(string table, List<string> columns, List<string> conditions)
        {
            StringBuilder sqlBuilder = new StringBuilder("UPDATE ");
            sqlBuilder.AppendFormat("[dbo].[{0}]", table);
            sqlBuilder.AppendLine(" SET ");

            int pIndex = 0;
            for (int index = 0; index < columns.Count; index++)
            {
                string name = columns[index];
                if (index == 0)
                {
                    sqlBuilder.AppendFormat("[{0}] = ", name);
                    sqlBuilder.Append("{");
                    sqlBuilder.AppendFormat("{0}", pIndex);
                    sqlBuilder.Append("}");
                    pIndex++;
                }
                else
                {
                    sqlBuilder.AppendLine(",");
                    sqlBuilder.AppendFormat("[{0}] =", name);
                    sqlBuilder.Append("{");
                    sqlBuilder.AppendFormat("{0}", pIndex);
                    sqlBuilder.Append("}");
                    pIndex++;
                }
            }
            sqlBuilder.AppendLine(" WHERE ");
            for (int index = 0; index < conditions.Count; index++)
            {
                string condition = conditions[index];
                if (index == 0)
                {
                    sqlBuilder.AppendFormat("[{0}] = ", condition);
                    sqlBuilder.Append("{");
                    sqlBuilder.AppendFormat("{0}", pIndex);
                    sqlBuilder.Append("}");
                    pIndex++;
                }
                else
                {
                    sqlBuilder.AppendLine(" and ");
                    sqlBuilder.AppendFormat("[{0}] = ", condition);
                    sqlBuilder.Append("{");
                    sqlBuilder.AppendFormat("{0}", pIndex);
                    sqlBuilder.Append("}");
                    pIndex++;
                }
            }

            return sqlBuilder.ToString();
        }

        /// <summary>
        /// 获取更新语句(@参数)
        /// </summary>
        /// <param name="table"></param>
        /// <param name="columns"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public static string GetUpdateSQL_V2(string table, List<string> columns, List<string> conditions)
        {
            StringBuilder sqlBuilder = new StringBuilder("UPDATE ");
            sqlBuilder.AppendFormat("[dbo].[{0}]", table);
            sqlBuilder.AppendLine(" SET ");
 
            for (int index = 0; index < columns.Count; index++)
            {
                string name = columns[index];
                if (index == 0)
                {
                    sqlBuilder.AppendFormat("[{0}] = @{0}", name);
                }
                else
                {
                    sqlBuilder.AppendLine(",");
                    sqlBuilder.AppendFormat("[{0}] = @{0}", name);
                }
            }
            sqlBuilder.AppendLine(" WHERE ");
            for (int index = 0; index < conditions.Count; index++)
            {
                string condition = conditions[index];
                if (index == 0)
                {
                    sqlBuilder.AppendFormat("[{0}] = @{0}", condition);
                }
                else
                {
                    sqlBuilder.AppendLine(" and ");
                    sqlBuilder.AppendFormat("[{0}] = @{0}", condition);
                }
            }

            return sqlBuilder.ToString();
        }

        /// <summary>
        /// 获取更新语句(@参数)
        /// </summary>
        /// <param name="table"></param>
        /// <param name="column"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public static string GetSelectSQL_V2(string table, string column, List<string> conditions)
        {
            StringBuilder sqlBuilder = new StringBuilder("select  ");

            sqlBuilder.AppendFormat("[{0}]", column);

            sqlBuilder.AppendFormat(" from [dbo].[{0}]", table);
            sqlBuilder.AppendLine(" WHERE ");
            for (int index = 0; index < conditions.Count; index++)
            {
                string condition = conditions[index];
                if (index == 0)
                {
                    sqlBuilder.AppendFormat("[{0}] = @{0}", condition);
                }
                else
                {
                    sqlBuilder.AppendLine(" and ");
                    sqlBuilder.AppendFormat("[{0}] = @{0}", condition);
                }
            }

            return sqlBuilder.ToString();
        }

        public static string GetInsertSQL(string table, List<string> names)
        {
            StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ");
            sqlBuilder.AppendFormat("[dbo].[{0}]", table);
            sqlBuilder.AppendLine("(");
            for (int index = 0; index < names.Count; index++)
            {
                string name = names[index];
                if(index == 0)
                {
                    sqlBuilder.AppendFormat("[{0}]", name);
                }
                else
                {
                    sqlBuilder.AppendLine(",");
                    sqlBuilder.AppendFormat("[{0}]", name);
                }
            }
            sqlBuilder.AppendLine(") VALUES (");
            for (int index = 0; index < names.Count; index++)
            {
                string name = names[index];
                if (index == 0)
                {
                    sqlBuilder.Append("{");
                    sqlBuilder.AppendFormat("{0}", index);
                    sqlBuilder.Append("}");
                }
                else
                {
                    sqlBuilder.AppendLine(",");
                    sqlBuilder.Append("{");
                    sqlBuilder.AppendFormat("{0}", index);
                    sqlBuilder.Append("}");
                }
            }
            sqlBuilder.AppendLine(")");

            return sqlBuilder.ToString();
        }
        public static string GetInsertSQLAtName(string table, List<string> names)
        {
            StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ");
            sqlBuilder.AppendFormat("[dbo].[{0}]", table);
            sqlBuilder.AppendLine("(");
            for (int index = 0; index < names.Count; index++)
            {
                string name = names[index];
                if (index == 0)
                {
                    sqlBuilder.AppendFormat("[{0}]", name);
                }
                else
                {
                    sqlBuilder.AppendLine(",");
                    sqlBuilder.AppendFormat("[{0}]", name);
                }
            }
            sqlBuilder.AppendLine(") VALUES (");
            for (int index = 0; index < names.Count; index++)
            {
                string name = names[index];
                if (index == 0)
                {
                    sqlBuilder.AppendFormat("@{0}", names[index]);
                }
                else
                {
                    sqlBuilder.AppendLine(",");
                    sqlBuilder.AppendFormat("@{0}", names[index]);
                }
            }
            sqlBuilder.AppendLine(")");

            return sqlBuilder.ToString();
        }

        /// <summary>
        /// 获得查询语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <param name="conditions"></param>
        /// <param name="pageColumn"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public static string GetSelectPagedSQL(string tableName, List<string> columns, List<string> conditions, string pageColumn, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendLine("SELECT T.* From (");

            sqlBuilder.AppendFormat("SELECT ROW_NUMBER() OVER(Order BY {0}) as _RowNumber,", pageColumn);

            sqlBuilder.AppendLine();

            for (int index = 0; index < columns.Count; index++)
            {
                if(index == 0)
                {
                    sqlBuilder.AppendFormat("[{0}] ", columns[index]);
                }
                else
                {
                    sqlBuilder.AppendFormat(",[{0}] ", columns[index]);

                }
            }

            sqlBuilder.AppendFormat("FROM [dbo].[{0}] ", tableName);

            if(conditions!=null && conditions.Count > 0)
            {
                sqlBuilder.AppendLine("WHERE ");
                for (int index = 0; index < conditions.Count; index++)
                {
                    string name = conditions[index];
                    if (index == 0)
                    {
                        sqlBuilder.AppendFormat("[{0}]=@{0}", name);
                    }
                    else
                    {
                        sqlBuilder.AppendLine(" and");
                        sqlBuilder.AppendFormat("[{0}]=@{0}", name);
                    }
                }
            }

            sqlBuilder.AppendLine();
            sqlBuilder.AppendFormat(")  as T WHERE T._RowNumber > {0} and T._RowNumber <= {1}", start, start + limit);

            return sqlBuilder.ToString();
        }

        public static string GetSelectPagedSQLWithWhere(string tableName, List<string> columns, string sqlWhere, string pageColumn, int start, int limit)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendLine("SELECT T.* From (");

            sqlBuilder.AppendFormat("SELECT ROW_NUMBER() OVER(Order BY {0}) as _RowNumber,", pageColumn);

            sqlBuilder.AppendLine();

            for (int index = 0; index < columns.Count; index++)
            {
                if (index == 0)
                {
                    sqlBuilder.AppendFormat("[{0}] ", columns[index]);
                }
                else
                {
                    sqlBuilder.AppendFormat(",[{0}] ", columns[index]);

                }
            }

            sqlBuilder.AppendFormat("FROM [dbo].[{0}] ", tableName);

            if (!string.IsNullOrEmpty(sqlWhere))
            {
                sqlBuilder.AppendLine("WHERE ");
                sqlBuilder.AppendLine(sqlWhere);
            }

            sqlBuilder.AppendLine();
            sqlBuilder.AppendFormat(")  as T WHERE T._RowNumber > {0} and T._RowNumber <= {1}", start, start + limit);

            return sqlBuilder.ToString();
        }

        public static string GetSelectSQL(string tableName, string sqlSelect, string sqlWhere)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendFormat("SELECT {0}", sqlSelect);
            sqlBuilder.AppendLine();

            sqlBuilder.AppendFormat("FROM [dbo].[{0}] ", tableName);

            if (!string.IsNullOrEmpty(sqlWhere))
            {
                sqlBuilder.AppendLine("WHERE ");
                sqlBuilder.AppendLine(sqlWhere);
            }

            sqlBuilder.AppendLine();

            return sqlBuilder.ToString();
        }

        public static string GetSelectTotalCountSQLWithWhere(string tableName, string sqlWhere, string pkColumn)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendFormat("SELECT Count([{0}]) as Total ", pkColumn);

            sqlBuilder.AppendFormat("FROM [dbo].[{0}] ", tableName);

            if (!string.IsNullOrEmpty(sqlWhere))
            {
                sqlBuilder.AppendLine("WHERE ");
                sqlBuilder.AppendLine(sqlWhere);
            }

            sqlBuilder.AppendLine();
            return sqlBuilder.ToString();
        }
        /// <summary>
        /// 参数是@xxx,返回列名PK
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="conditions"></param>
        /// <param name="pkColumn"></param>
        /// <returns></returns>
        public static string GetSelectPKSQL_V2(string tableName, List<string> conditions, string pkColumn)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendFormat("SELECT {0} as PK ", pkColumn);

            sqlBuilder.AppendFormat("FROM [dbo].[{0}] ", tableName);

            if (conditions != null && conditions.Count > 0)
            {
                sqlBuilder.AppendLine("WHERE ");
                for (int index = 0; index < conditions.Count; index++)
                {
                    string name = conditions[index];
                    if (index == 0)
                    {
                        sqlBuilder.AppendFormat("[{0}]=@{0}", name);
                    }
                    else
                    {
                        sqlBuilder.AppendLine(" and");
                        sqlBuilder.AppendFormat("[{0}]=@{0}", name);
                    }
                }
            }

            sqlBuilder.AppendLine();
            return sqlBuilder.ToString();
        }

        public static string GetSelectTotalCountSQL(string tableName, List<string> conditions, string pkColumn)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendFormat("SELECT Count([{0}]) as Total ", pkColumn);

            sqlBuilder.AppendFormat("FROM [dbo].[{0}] ", tableName);

            if (conditions != null && conditions.Count > 0)
            {
                sqlBuilder.AppendLine("WHERE ");
                for (int index = 0; index < conditions.Count; index++)
                {
                    string name = conditions[index];
                    if (index == 0)
                    {
                        sqlBuilder.AppendFormat("[{0}]=@{0}", name);
                    }
                    else
                    {
                        sqlBuilder.AppendLine(" and");
                        sqlBuilder.AppendFormat("[{0}]=@{0}", name);
                    }
                }
            }

            sqlBuilder.AppendLine();
            return sqlBuilder.ToString();
        }

        public static string GetSelectSQL(string tableName, List<string> columns, string sqlWhere)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendFormat("SELECT ");

            sqlBuilder.AppendLine();

            for (int index = 0; index < columns.Count; index++)
            {
                if (index == 0)
                {
                    sqlBuilder.AppendFormat("[{0}] ", columns[index]);
                }
                else
                {
                    sqlBuilder.AppendFormat(",[{0}] ", columns[index]);

                }
            }

            sqlBuilder.AppendFormat("FROM [dbo].[{0}] ", tableName);

            if (!string.IsNullOrEmpty(sqlWhere))
            {
                sqlBuilder.AppendFormat(" where {0}", sqlWhere);
            }

            sqlBuilder.AppendLine();

            return sqlBuilder.ToString();
        }

        public static string GetSelectSQL(string tableName, List<string> columns, List<string> conditions)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendFormat("SELECT ");

            sqlBuilder.AppendLine();

            for (int index = 0; index < columns.Count; index++)
            {
                if (index == 0)
                {
                    sqlBuilder.AppendFormat("[{0}] ", columns[index]);
                }
                else
                {
                    sqlBuilder.AppendFormat(",[{0}] ", columns[index]);

                }
            }

            sqlBuilder.AppendFormat("FROM [dbo].[{0}] ", tableName);

            if(conditions!=null)
            {
                sqlBuilder.AppendLine("WHERE ");
                for (int index = 0; index < conditions.Count; index++)
                {
                    string name = conditions[index];
                    if (index == 0)
                    {
                        sqlBuilder.AppendFormat("[{0}]=@{0}", name);
                    }
                    else
                    {
                        sqlBuilder.AppendLine(" and");
                        sqlBuilder.AppendFormat("[{0}]=@{0}", name);
                    }
                }
            }

            sqlBuilder.AppendLine();

            return sqlBuilder.ToString();
        }

        public static string GetSelectSQLWithWhere(string tableName, List<string> columns, string sqlWhere)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendFormat("SELECT ");

            sqlBuilder.AppendLine();

            for (int index = 0; index < columns.Count; index++)
            {
                if (index == 0)
                {
                    sqlBuilder.AppendFormat("[{0}] ", columns[index]);
                }
                else
                {
                    sqlBuilder.AppendFormat(",[{0}] ", columns[index]);

                }
            }

            sqlBuilder.AppendFormat("FROM [dbo].[{0}] ", tableName);

            if(!string.IsNullOrEmpty(sqlWhere))
            {
                sqlBuilder.AppendLine("WHERE ");
                sqlBuilder.AppendLine(sqlWhere);
            }
            sqlBuilder.AppendLine();

            return sqlBuilder.ToString();
        }

    }
}
